﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class DesignationGateway:DBGateway
    {
        public bool HasThisDesignationAlreadyExists(Designation designation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select Title from tblDesignations where Title='"+designation.Title+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public bool Save(Designation designation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Insert into tblDesignations values(@Title,@grade)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Title", SqlDbType.VarChar).Value = designation.Title;
                CommandObj.Parameters.Add("@grade", SqlDbType.VarChar).Value = designation.Grade;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured while Saving Designation, Please Check Input & Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public bool Edit(Designation designation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("update tblDesignations set Title=@Title,Grade=@Grade where Id='"+designation.Id+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Title", SqlDbType.VarChar).Value = designation.Title;
                CommandObj.Parameters.Add("@Grade", SqlDbType.VarChar).Value = designation.Grade;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured while Editing Designation, Please Check Input & Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public bool Delete(Designation designation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Delete from tblDesignations where Id='" + designation.Id + "'");
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error occured while Deleting Designation, Please Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }

        public List<Designation> GetAllDesignations()
        {
            try
            {
                List<Designation> designations=new List<Designation>();
                ConnectionObj.Open();
                CommandObj.CommandText = @"select * from tblDesignations     order by Title";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Designation designation=new Designation();
                    designation.Id = Convert.ToInt32(reader[0]);
                    designation.Title = reader[1].ToString();
                    designation.Grade = reader[2].ToString();
                    designations.Add(designation);
                }
                return designations;
            }
            catch (Exception)
            {
                throw new Exception("Error occured while Reading Designation, Please Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
         }

        public int GetDesignationId(Designation designation)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select Id from tblDesignations where Title='"+designation.Title+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                int id = 0;
                while (reader.Read())
                {
                    id = Convert.ToInt32(reader[0]);
                }
                return id;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured in DB during reading Designation Id !");
            }
            finally
            {
              ConnectionObj.Close();  
            }
        }
    }
}